package cn.biplam.back.operator.tree;

import java.util.ArrayList;

import cn.biplam.common.client.DataFilter;
import cn.biplam.common.client.Operator;
import cn.biplam.common.client.OperatorResult;
import cn.biplam.common.interfaces.DataOperator;
import cn.biplam.common.tools.TOOLS;
import cn.biplam.common.wrapper.ErrorMessage;
import cn.biplam.common.wrapper.KvMap;
import cn.biplam.common.wrapper.sqlresult.SqlResult;

/**
 * 生成基于父子表数据源的树形控件数据的查询SQL类<br>
 * <ul> 用法：
 * <li>1 new OptTreeFiniation()  创建SQL生成类 
 * <li>2 this.set()  设置所需参数
 *     表名，
 *     父子关系字段名 ，
 *     向下级别N  -1 全部 1向下1级,2向下2级,n向下n级，
 *     过滤条件，
 *     排序字段名
 * <li>3 getSql()  获取查询SQL  查询结果按照级别和传入排序字段排序
 * @author XiaoRuxing 2009-7-1下午05:10:50
 * @version 1.0
 */
public class OptTreeFiniation extends DataOperator{

	private static final String Op_ID = "282403";

	/** 级别排序字段名称 用于结果的排序*/
	private static final String Order_Field_Name = "order_level";
	/** 获取子树深度 */
	private static final String Select_Sub_Tree_Depth= Op_ID+"01";

	private String tableName;
	private String levelDown;
	private String filterSql;
	private String fieldName;
	private String rootValue;
	private String parentFieldName;
	private String orderField;

	public OptTreeFiniation() throws ErrorMessage {
		super("treeFiniation");
	}

	@Override
	protected OperatorResult _exchange() throws ErrorMessage {
		ArrayList<String> sql = new ArrayList<String>();
		if(this.opId.equals(Select_Sub_Tree_Depth)){
			this._getTreeDepth(sql, filter);
			return this.doing(sql,Operator_Type.Select);
		}else{
			return null;
		}
	}

	/**
	 * 设置SQL生成需要的参数
	 * @param tableName  表名
	 * @param levelDown  向下级别 查询时向下多查询一个级别
	 * @param filterSql  过滤条件
	 * @param fieldId    子字段名  
	 * @param parentId   父字段名
	 * @param parentValue 根节点值
	 * @param otherFields 其他需要字段
	 */
	public void set(String tableName,
			String levelDown,
			String filterSql,
			String rootValue,
			String fieldName,
			String parentName){
		this.set(tableName,levelDown,filterSql,rootValue,fieldName,parentName,null);
	}
	
	/**
	 * 设置SQL生成需要的参数
	 * @param tableName  表名
	 * @param levelDown  向下级别 查询时向下多查询一个级别
	 * @param filterSql  过滤条件
	 * @param fieldId    子字段名  
	 * @param parentId   父字段名
	 * @param parentValue 根节点值
	 * @param orderField 排序字段
	 * @param otherFields 其他需要字段
	 */
	public void set(String tableName,
			String levelDown,
			String filterSql,
			String rootValue,
			String fieldName,
			String parentName,
			String orderField){
		this.tableName = tableName;
		this.levelDown = levelDown;
		this.filterSql = filterSql;
		this.rootValue = rootValue;
		this.fieldName = fieldName;
		this.orderField = orderField;
		this.parentFieldName = parentName;
	}

	/**
	 * 查询当前节点是否有子指定级数的节点，如指定1级则返回是否有下一级节点、如果指定2级则返回是否有下两级节点 依次类推...
	 * @param sql
	 * @param filter 过滤参数： 节点类型、要向下查询的级数
	 * @throws ErrorMessage 
	 */
	private void _getTreeDepth(ArrayList<String> sql,DataFilter filter) throws ErrorMessage{
		StringBuilder sp=new StringBuilder();
		String tableName = filter.getValue("table_name");
		String fieldName = filter.getValue("fieldName");
		String parentFieldName = filter.getValue("parentFieldName");
		String fieldValue = filter.getValue("fieldValue");
		int levelDown=TOOLS.stringToInt(filter.getValue("depth"));
		if(levelDown==-1){
			levelDown=1;
		}
		sp.append("select 1 from (");
		sp.append("select a"+(levelDown+1)+".* ");
		sp.append(" from "+tableName+" a1");
		int j;
		for(j=1;j<=levelDown;j++){
			sp.append(" join "+tableName+" a"+(j+1));
			sp.append(" on a"+(j)+"."+fieldName+"=a"+(j+1)+"."+parentFieldName+" ");
		}
		sp.append("where a1."+fieldName+"='"+fieldValue+"'");
		sp.append(") t ");
		sql.add(sp.toString());
	}

	/**
	 * 获取指定节点的子节点级数，即指定节点为根节点时子树深度 
	 */
	private int _getSubTreeDepth() throws ErrorMessage{
		DataFilter filter = new DataFilter();
		filter.put("where",this.filterSql);
		filter.put("table_name", this.tableName);
		filter.put("fieldName", this.fieldName);
		filter.put("parentFieldName", this.parentFieldName);
		filter.put("fieldValue", this.rootValue);
		SqlResult sr=Operator.get(Select_Sub_Tree_Depth, "", filter).getSelectResult();
		int depth=0;
		while(sr!=null&&!sr.isNull()){
			++depth;
			filter.put("depth",TOOLS.intToString(depth));
			sr=Operator.get(Select_Sub_Tree_Depth, "", filter).getSelectResult();
		}
		return depth-1;
	}

	private void _getTreeSql(StringBuilder sp,int levelDown){
		String parent= null;
		levelDown = levelDown + 1;
		parent="a1."+this.fieldName;
		for(int i=1;i<=levelDown;i++){
			sp.append(" select a"+(levelDown-i+1)+".* ,");
			sp.append((levelDown-i)+" "+Order_Field_Name);
			sp.append(" from "+this.tableName+" a1");
			int j;
			for(j=1;j<=levelDown-i;j++){
				sp.append(" join "+tableName+" a"+(j+1));
				sp.append(" on a"+(j)+"."+this.fieldName+"=a"+(j+1)+"."+this.parentFieldName+" ");
			}
			sp.append("  where "+parent+"='"+this.rootValue+"'");
			if(i<levelDown){
				sp.append(" \n union \n");
			}
		}
	}
	
	/**
	 * 查询指定节点的所有父亲节点
	 * @param 根据节点ID查询节点的动作ID
	 * @return 所有父节点的容器
	 */
	public KvMap<String, String> getParents(String queryNodeActionId) throws ErrorMessage{
		KvMap<String, String> parents = new KvMap<String, String>();
		DataFilter filter = new DataFilter();
		filter.put(this.fieldName, this.rootValue);
		OperatorResult mParent = Operator.get(queryNodeActionId, userId, filter);
		String curParent = null;
		while(TOOLS.exist(mParent)){
			curParent = mParent.getValue(this.parentFieldName);
			parents.put(curParent, curParent);
			filter.put(this.fieldName, curParent);
			mParent = Operator.get(queryNodeActionId, userId, filter);
		}
		return parents;
	}

	/**
	 * 生成基于父子表数据源的树形控件数据的查询SQL
	 * @return
	 * @throws ErrorMessage
	 */
	public String getSql() throws ErrorMessage{
		StringBuilder sp = new StringBuilder();
		int levelDown = TOOLS.stringToInt(this.levelDown);
		int depth=_getSubTreeDepth();
		if(levelDown==-1){
			levelDown=depth;
		}else{
			levelDown=levelDown+1>depth?depth:levelDown+1;
		}
		StringBuilder treeSql = new StringBuilder();
		_getTreeSql(treeSql, levelDown);
		if(TOOLS.StringIsEmpty(treeSql.toString())){
			return null;
		}
		sp.append(" select * from (");
		sp.append(treeSql);
		sp.append(") a ");
		if(this.filterSql!=null){
			sp.append(" where "+this.filterSql);
		}
		sp.append(" order by "+Order_Field_Name);
		if(this.orderField!=null){
			sp.append(","+this.orderField);
		}
		return sp.toString();
	}
}
